using System;
using System.Collections.Generic;
using System.Text;
using System.Data ;
using System.Data.OleDb;
using System.Configuration;

namespace ZhiAo.CoreLibrary
{
    public class SQLServerDB
    {
        private string connectionstring;
        private OleDbConnection conn;
        private OleDbCommand cmd;
        private OleDbTransaction ts;
        private OleDbDataAdapter adp;

        public  static string StaticConnectionString;

        public string ConnectionString
        {
            get { return connectionstring; }
        }


        public SQLServerDB()
        {
            this.connectionstring = StaticConnectionString;
            conn = new OleDbConnection(connectionstring);
            cmd = new OleDbCommand();
            adp = new OleDbDataAdapter();
            cmd.Connection = conn;
        }

        public int ExecuteNonQuery(string sql)
        {
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.CommandText = sql;
            Open();
            return cmd.ExecuteNonQuery();
        }

        public DataSet GetDataSet(string sql)
        {
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.CommandText = sql;
            Open();
            adp.SelectCommand = cmd;
            DataSet data = new DataSet();
            adp.Fill(data);
            return data;
        }

        public DataTable  GetDataTable(string sql)
        {
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.CommandText = sql;
            Open();
            adp.SelectCommand = cmd;
            DataSet data = new DataSet();
            adp.Fill(data);
            return data.Tables[0];
        }

        public string GetSequence(string sequenceName)
        {
            string sql = "SELECT " + sequenceName + ".NEXTVAL FROM DUAL";
            DataSet data = GetDataSet(sql);
            return data.Tables[0].Rows[0][0].ToString();
        }

        public string SQLQ(string key)
        {
            return "'" + key.Replace("'", "''") + "'";
        }


        private void Open()
        {
            if (conn.State != System.Data.ConnectionState.Open) conn.Open();
        }

        public void Close()
        {
            conn.Close();
        }

        public void BeginTransaction()
        {
            Open();
            ts = conn.BeginTransaction();
            cmd.Transaction = ts;
        }
 

        public void CommitTransaction()
        {
            try
            {
                ts.Commit();
            }
            catch
            {
            }
            finally
            {
                Close();
            }
        }
        public void RollbackTransaction()
        {
            try
            {
                ts.Rollback();
            }
            catch
            {
            }
            finally
            {
                Close();
            }
        }
    }
}
